﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace Lottery
{
    class Access
    {
        /// <summary>
        /// 存储数据库连接（保护类，只有由它派生的类才能访问）
        /// </summary>
        protected OleDbConnection Connection;

        /// <summary>
        /// 构造函数：数据库的默认连接
        /// </summary>
        /// <param name="connectionString">文件路径，绝对或相对路径</param>
        public Access(string path)
        {
            try
            {
                string connectionString = "";
                if (path.Contains("Provider="))
                    connectionString = path;
                else
                    connectionString = @"Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + Types.ToFullPath(path) + ";";
                Connection = new OleDbConnection(connectionString);
            }
            catch (Exception ex)
            {
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
                LogService.ErrorLog("Access::Access(" + path + ")", ex);
            }
        }

        /// <summary>
        /// 执行SQL语句没有返回结果，如：执行删除、更新、插入等操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>操作成功标志</returns>
        public bool ExeSQL(string strSQL)
        {
            bool resultState = false;
            OleDbTransaction myTrans = null;
            OleDbCommand command = null;
            try
            {
                Connection.Open();
                myTrans = Connection.BeginTransaction();
                command = new OleDbCommand(strSQL, Connection, myTrans);
                command.ExecuteNonQuery();
                myTrans.Commit();
                resultState = true;
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                resultState = false;
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
                LogService.ErrorLog("Access::ExeSQL(" + strSQL + ")", ex);
            }
            finally
            {
                Connection.Close();
            }
            return resultState;
        }

        /// <summary>
        /// 执行SQL语句返回结果到DataReader中
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>dataReader</returns>
        private OleDbDataReader ReturnDataReader(string strSQL)
        {
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;
            try
            {
                Connection.Open();
                command = new OleDbCommand(strSQL, Connection);
                dataReader = command.ExecuteReader();
                Connection.Close();
            }
            catch (Exception ex)
            {
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
                LogService.ErrorLog("Access::ReturnDataReader(" + strSQL + ")", ex);
            }
            return dataReader;
        }

        /// <summary>
        /// 执行SQL语句返回结果到DataSet中
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>DataSet</returns>
        public DataTable ReturnDataTable(string strSQL)
        {
            DataTable dataTable = new DataTable();
            OleDbDataAdapter OleDbDA = null;
            try
            {
                Connection.Open();
                OleDbDA = new OleDbDataAdapter(strSQL, Connection);
                OleDbDA.Fill(dataTable);
                Connection.Close();
            }
            catch (Exception ex)
            {
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
                LogService.ErrorLog("Access::ReryturnDataSet(" + strSQL + ")", ex);
            }
            return dataTable;
        }

        /// <summary>
        /// 执行一查询语句，同时返回查询结果数目
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>sqlResultCount</returns>
        public int ReturnSqlResultCount(string strSQL)
        {
            int sqlResultCount = 0;
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;
            try
            {
                Connection.Open();
                command = new OleDbCommand(strSQL, Connection);
                dataReader = command.ExecuteReader();
                while (dataReader.Read())
                    sqlResultCount++;
                dataReader.Close();
            }
            catch (Exception ex)
            {
                sqlResultCount = 0;
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
                LogService.ErrorLog("Access::ReturnSqlResultCount(" + strSQL + ")", ex);
            }
            finally
            {
                Connection.Close();
            }
            return sqlResultCount;
        } 
    }
}
